package com.wanmait.spacesys.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.wanmait.spacesys.util.DbHelper;
import com.wanmait.spacesys.vo.Article;
import com.wanmait.spacesys.vo.Managerinfo;
import com.wanmait.spacesys.vo.Post;
import com.wanmait.spacesys.vo.Subject;
import com.wanmait.spacesys.vo.Userinfo;

public class ArticleDAO {

	public void addArticle(Article article) {
		String sql = "insert into article (manager_id,subject_id,title,content,pic) values(?,?,?,?,?)";
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			DbHelper.update(sql,article.getManagerId(),article.getSubjectId(),article.getTitle(),article.getContent(),article.getPic());			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(ps,conn);
		}
	}

	public List<Article> findById(Integer id) {
		List<Article> articles = new ArrayList();
		String sql = "select article.* from article where subject_id=?";
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()) {
				Article article = new Article();
				article.setId(rs.getInt("id"));
				article.setTitle(rs.getString("title"));
				article.setContent(rs.getString("content"));
				article.setPic(rs.getString("pic"));
				article.setManagerId(rs.getInt("manager_id"));
				article.setSubjectId(rs.getInt("subject_id"));
				articles.add(article);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs,ps,conn);
		}
		return articles;
	}

	public void updateArticle(Article article) {
		String sql = "update article set manager_id=?,subject_id=?,title=?,content=?,pic=? where id=?";
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
//		System.out.println("DAO:"+article.getId());
		try {
			ps = conn.prepareStatement(sql);
			DbHelper.update(sql,article.getManagerId(),article.getSubjectId(),article.getTitle(),article.getContent(),article.getPic(),article.getId());			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(ps,conn);
		}
		
	}

	public void updatePart(Article article) {
		String sql = "update article set manager_id=?,subject_id=?,title=?,content=? where id=?";
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			DbHelper.update(sql,article.getManagerId(),article.getSubjectId(),article.getTitle(),article.getContent(),article.getId());			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DbHelper.close(ps,conn);
		}
	}


	//获取文章的条数-->分页
//	public int getCount(){
//		int count = 0;
//		String sql = "select count(id) from article";
//		
//		Connection conn = DbHelper.getConnection();
//		PreparedStatement ps = null;
//		ResultSet rs = null;
//		try {
//			ps = conn.prepareStatement(sql);
//			rs = ps.executeQuery();
//			if(rs.next()) {
//			count = rs.getInt(1);
//			
//			}
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		finally {
//			DbHelper.close(rs, ps, conn);
//		}
//		return count;
//	}
	

	
	//根据类别的id获取文章的条数-->分页
		public int getpaging(Integer Id){
			int count = 0;
			String sql = "select count(*) as count from article where subject_id=?";
			
			Connection conn = DbHelper.getConnection();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1,Id);
				rs = ps.executeQuery();
				if(rs.next()) {
				count = rs.getInt("count");
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return count;
		}
	
	//根据最新时间进行排序 5篇文章
	public List<Article> newFindAll(int pageNum){
		String sql = "SELECT article.id,title,content,managerinfo.nickname,pub_time,pic,managerinfo.id FROM article "
				+ "LEFT JOIN managerinfo ON article.manager_id=managerinfo.id "
				+ "ORDER BY pub_time desc "
				+ "LIMIT "+(pageNum-1)*5+",5";
		List<Article> articles = new ArrayList<>();
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs= ps.executeQuery();
			while(rs.next()) {
				Article article = new Article();
				article.setId(rs.getInt("id"));
				article.setTitle(rs.getString("title"));
				article.setContent(rs.getString("content"));
				article.setPubTime(rs.getTimestamp("pub_time"));
				article.setPic(rs.getString("pic"));
				
				Managerinfo managerinfo = new Managerinfo();
				managerinfo.setId(rs.getInt("id"));
				managerinfo.setNickname(rs.getString("nickname"));
				article.setManagerinfo(managerinfo);
				
				articles.add(article);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
		}
		return articles;
	}
	
	
	//每个类别列表最新的显示
	public List<Article> newSubject(Integer subjectId,int pageNum){
		String sql = "SELECT article.id,title,content,managerinfo.nickname,pub_time,pic,manager_id,subject_id FROM article "
				+ "LEFT JOIN managerinfo ON article.manager_id=managerinfo.id "
				+ "WHERE subject_id =? "
				+ "ORDER BY pub_time desc "
				+ "LIMIT "+(pageNum-1)*5+",5";
		List<Article> articles = new ArrayList<>();
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, subjectId);
			rs = ps.executeQuery();
			while(rs.next()) {
				Article article = new Article();
				article.setId(rs.getInt("id"));
				article.setContent(rs.getString("content"));
				article.setTitle(rs.getString("title"));
				article.setPubTime(rs.getTimestamp("pub_time"));
				article.setPic(rs.getString("pic"));
				
				Managerinfo managerinfo = new Managerinfo();
				managerinfo.setId(rs.getInt("id"));
				managerinfo.setNickname(rs.getString("nickname"));
				article.setManagerinfo(managerinfo);
				
				articles.add(article);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
			
		}
		return articles;
	}
	
	
	//模糊搜索
	public List<Article> findSearch(Integer articleId,String checkId){
		String sql  = "SELECT * FROM article LEFT JOIN managerinfo ON article.manager_id=managerinfo.id "
				+"LEFT JOIN subject ON article.subject_id=subject.id "
				+"WHERE title like ?";
		List<Article> articles = new ArrayList<>();
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, "%"+checkId+"%%");
			rs=ps.executeQuery();
			while(rs.next()) {
				Article article = new Article();
				article.setId(rs.getInt("id"));
				article.setContent(rs.getString("content"));
				article.setTitle(rs.getString("title"));
				article.setPubTime(rs.getTimestamp("pub_time"));
				article.setPic(rs.getString("pic"));
				
				Managerinfo managerinfo = new Managerinfo();
				managerinfo.setId(rs.getInt("id"));
				managerinfo.setNickname(rs.getString("nickname"));
				article.setManagerinfo(managerinfo);
				
				
				Subject subject = new Subject();
				subject.setId(rs.getInt("id"));
				subject.setName(rs.getString("name"));
				article.setSubject(subject);
				articles.add(article);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
			
		}
		return articles;
	}
	
	//显示宇航产品的方法
	public List<Article> findProduct(Integer subjectId,int pageNum){
		String sql = "SELECT article.id,title,pic FROM article WHERE subject_id=? "
				 	+"LIMIT "+(pageNum-1)*6+",6";
		List<Article> articles = new ArrayList<>();
		Connection conn = DbHelper.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, subjectId);
			rs=ps.executeQuery();
			while(rs.next()) {
				Article article = new Article();
				article.setId(rs.getInt("id"));
				article.setTitle(rs.getString("title"));
				article.setPic(rs.getString("pic"));
				articles.add(article);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
		}
		return articles;
	}
	
	
	//每页五篇文章进行显示

	/*
	 * public List<Article> randomList(){ String sql =
	 * "SELECT article.id,title,managerinfo.nickname,pub_time,pic " +
	 * "managerinfo.id  " + "FROM article " +
	 * "LEFT JOIN managerinfo ON article.managerinfo_id=managerinfo.id " +
	 * "ORDER BY RAND() " + "LIMIT 0,5 "; List<Article> randomArticles = new
	 * ArrayList<>(); Connection conn = DbHelper.getConnection(); PreparedStatement
	 * ps = null; ResultSet rs = null; try { ps = conn.prepareStatement(sql); rs =
	 * ps.executeQuery(); while(rs.next()) { Article article = new Article();
	 * article.setId(rs.getInt("id")); article.setTitle(rs.getString("title"));
	 * article.setPubTime(rs.getTimestamp("pub_time"));
	 * article.setPic(rs.getString("pic"));
	 * 
	 * Managerinfo managerinfo = new Managerinfo();
	 * managerinfo.setNickname(rs.getString("nickname"));
	 * managerinfo.setId(rs.getInt("id")); article.setManagerinfo(managerinfo);
	 * 
	 * randomArticles.add(article); } } catch (SQLException e) { // TODO
	 * Auto-generated catch block e.printStackTrace(); }
	 * 
	 * return randomArticles; }
	 */

 

	//航天英雄文章查询
		public List<Article> spacehero()
		{	
			List<Article> article = new ArrayList();
			
			String sql = "SELECT article.id,title,content,managerinfo.nickname,subject.name,pic,pub_time,managerinfo.id FROM article\r\n" + 
					"LEFT JOIN managerinfo ON article.manager_id= managerinfo.id\r\n" + 
					"LEFT JOIN subject ON article.subject_id=subject.id\r\n" + 
					"WHERE subject.id=?";
			Connection conn = DbHelper.getConnection();
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			try {
				ps=conn.prepareStatement(sql);
				//ps.setInt(1, id);
				rs=ps.executeQuery();
				while(rs.next())
				{
					Article articles = new Article();
					articles.setId(rs.getInt("id"));
					articles.setTitle(rs.getString("title"));
					articles.setContent(rs.getString("content"));
					articles.setPic(rs.getString("pic"));
					articles.setManagerId(rs.getInt("manager_id"));
					articles.setSubjectId(rs.getInt("subject_id"));
					article.add(articles);
				}
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			
			return article;
		}
		
		
		//航天英雄文章查询
		public List<Article> spacehero(Integer subjectId )
		{	
			List<Article> article = new ArrayList();
			
			String sql = "SELECT article.id,subject_id,article.manager_id,title,content,managerinfo.nickname,subject.name,pic,pub_time,managerinfo.id FROM article\r\n" + 
					"LEFT JOIN managerinfo ON article.manager_id= managerinfo.id\r\n" + 
					"LEFT JOIN subject ON article.subject_id=subject.id\r\n" +
					"WHERE subject.id=?";
			
			     
			Connection conn = DbHelper.getConnection();
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			try {
				ps=conn.prepareStatement(sql);
				ps.setInt(1, subjectId);
				rs=ps.executeQuery();
				while(rs.next())
				{
					
					Article articles = new Article();
					articles.setId(rs.getInt("id"));
					articles.setTitle(rs.getString("title"));
					articles.setContent(rs.getString("content"));
					articles.setPic(rs.getString("pic"));
					articles.setManagerId(rs.getInt("article.manager_id"));
					articles.setSubjectId(rs.getInt("subject_id"));
					articles.setPubTime(rs.getTimestamp("pub_time"));
					//articles.set(rs.getString("nickname"));
					
					
					Managerinfo managerinfo = new Managerinfo();
					managerinfo.setId(rs.getInt("id"));
					managerinfo.setNickname(rs.getString("nickname"));
					
					articles.setManagerinfo(managerinfo);
					
					article.add(articles);
				}
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			
			return article;
			
		}
		
		
//			//航天英雄 统计
			public int showCount(Integer Id)
			{
				int count = 0;
		        String sql="select count(*) as count from article where subject_id=?";		
			    Connection conn = DbHelper.getConnection();
				PreparedStatement ps = null;
			    ResultSet rs = null;	
			try {
				ps=conn.prepareStatement(sql);
				ps.setInt(1, Id);
				rs=ps.executeQuery();
				if(rs.next()) {
					count=rs.getInt("count");
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			finally {
				DbHelper.close(rs, ps, conn);
			}
			
				return count;
			}
			
			public List<Article> newSubjectt(Integer subjectId,int pageNum){
				String sql = "SELECT article.id,title,content,managerinfo.nickname,pub_time,pic,manager_id,subject_id FROM article "
						+ "LEFT JOIN managerinfo ON article.manager_id=managerinfo.id "
						+ "WHERE subject_id =? "
						+ "ORDER BY pub_time desc "
						+ "LIMIT "+(pageNum-1)*4+",4";
				List<Article> articles = new ArrayList<>();
				Connection conn = DbHelper.getConnection();
				PreparedStatement ps = null;
				ResultSet rs = null;
				try {
					ps = conn.prepareStatement(sql);
					ps.setInt(1, subjectId);
					rs = ps.executeQuery();
					while(rs.next()) {
						Article article = new Article();
						article.setId(rs.getInt("id"));
						article.setId(subjectId);
						article.setContent(rs.getString("content"));
						article.setTitle(rs.getString("title"));
						article.setPubTime(rs.getTimestamp("pub_time"));
						article.setPic(rs.getString("pic"));
						
						Managerinfo managerinfo = new Managerinfo();
						managerinfo.setId(rs.getInt("id"));
						managerinfo.setNickname(rs.getString("nickname"));
						article.setManagerinfo(managerinfo);
						
						articles.add(article);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				finally {
					DbHelper.close(rs, ps, conn);
					
				}
				return articles;
			}
			
			
			public List<Article> findAllArticleze(){
				List<Article> articles=new ArrayList<Article>();
				String sql="SELECT pic,id,content FROM article  order by pub_time DESC LIMIT 0,1";
				List<Map<String,Object>> datas=DbHelper.select(sql);
				for(Map<String,Object> row:datas) {
					Article article=new Article();
					article.setPic((String)row.get("pic"));
					article.setId((Integer)row.get("id"));
					
					article.setContent((String)row.get("content"));
					articles.add(article);
				}
				return articles;
		  	}
		  	
		  	public Article findByIds(Integer articleId) {
				Article article=null;
				Connection connection=DbHelper.getConnection();
				PreparedStatement preparedStatement=null;
				ResultSet resultSet=null;
				String sql="SELECT title,content,id,pic,pub_time FROM article WHERE id=?";
				try {
					preparedStatement=connection.prepareStatement(sql);
					preparedStatement.setInt(1, articleId);
					resultSet=preparedStatement.executeQuery();
					if(resultSet.next()) {
						article=new Article();
						article.setTitle(resultSet.getString("title"));
						article.setContent(resultSet.getString("content"));
						article.setId(resultSet.getInt("id"));
						article.setPic(resultSet.getString("pic"));
						article.setPubTime(resultSet.getTimestamp("pub_time"));
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

				return article;
			}


	
	
	

		

}


